﻿
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using ZX.DAL.DBExtend;

namespace ZX.CRM.Bussiness
{
    /// <summary>
    /// 推广中心信息统计
    /// </summary>
    public class BCrmRptTuiguang
    {
        #region "私海"
        /// <summary>
        /// 获取所有客户信息--私海  行业
        /// </summary>        
        /// <returns></returns>
        public DataTable ReturnTablePrivateTrade(int PowerType = 0)
        {
            string strsql = string.Empty;
            if (PowerType == 0)
                strsql = "select  top 15 CustomerTrade ,COUNT(id) as 'CustomerYuechang'  from V_CrmRptPrivateTuiguang  group by  CustomerTrade  order by  COUNT(ID) DESC ";
            return ReturnTable(strsql);
        }
        /// <summary>
        /// 获取所有客户信息--私海  规模
        /// </summary>        
        /// <returns></returns>
        public DataTable ReturnTablePrivatePerson( int PowerType = 0)
        {
            string strsql = string.Empty;
            if (PowerType == 0)
                strsql = "select  top 15 CustomerPorson ,COUNT(id) as 'CustomerYuechang'  from V_CrmRptPrivateTuiguang  group by  CustomerPorson  order by  COUNT(ID) DESC ";
            return ReturnTable(strsql);
        }
        /// <summary>
        /// 获取所有客户信息--私海  产值
        /// </summary>        
        /// <returns></returns>
        public DataTable ReturnTablePrivateProduct(int PowerType = 0)
        {
            string strsql = string.Empty;
            if (PowerType == 0)
                strsql = "select  top 15 CustomerProduct ,COUNT(id) as 'CustomerYuechang'  from V_CrmRptPrivateTuiguang  group by  CustomerProduct  order by  COUNT(ID) DESC ";
            return ReturnTable(strsql);
        }
        /// <summary>
        /// 获取所有客户信息--私海  城市（区域）
        /// </summary>        
        /// <returns></returns>
        public DataTable ReturnTablePrivateCity( int PowerType = 0)
        {
            string strsql = string.Empty;
            if (PowerType == 0)
                strsql = "select  top 15 Province+City as City,COUNT(id) as 'CustomerYuechang'  from V_CrmRptPrivateTuiguang  group by  Province+City  order by  COUNT(ID) DESC ";
            return ReturnTable(strsql);
        }
        #endregion
        #region "约场成功"
        /// <summary>
        /// 获取所有客户信息--约场成功  行业
        /// </summary>        
        /// <returns></returns>
        public DataTable ReturnTableYuechangTrade(int PowerType, DateTime Startdate,DateTime Enddate)
        {
            string strsql = string.Empty;
            if (PowerType == 0)
                strsql = "select  top 15 CustomerTrade ,COUNT(id) as 'CustomerYuechang'  from V_CrmRptyuechangTuiGuang  group by  CustomerTrade  order by  COUNT(ID) DESC ";
            else
                strsql = "select  top 15 CustomerTrade ,COUNT(id) as 'CustomerYuechang'  from V_CrmRptyuechangTuiGuang   where HmDate between '" + Startdate + "' and '" + Enddate + "' group by  CustomerTrade  order by  COUNT(ID) DESC";
            return ReturnTable(strsql);
        }
        /// <summary>
        /// 获取所有客户信息--约场成功  规模
        /// </summary>        
        /// <returns></returns>
        public DataTable ReturnTableYuechangPerson(int PowerType, DateTime Startdate, DateTime Enddate)
        {
            string strsql = string.Empty;
            if (PowerType == 0)
                strsql = "select  top 15 CustomerPerson ,COUNT(id) as 'CustomerYuechang'  from V_CrmRptyuechangTuiGuang  group by  CustomerPerson  order by  COUNT(ID) DESC ";
            else
                strsql = "select  top 15 CustomerPerson ,COUNT(id) as 'CustomerYuechang'  from V_CrmRptyuechangTuiGuang   where HmDate between '" + Startdate + "' and '" + Enddate + "' group by  CustomerPerson  order by  COUNT(ID) DESC";
            
            return ReturnTable(strsql);
        }
        /// <summary>
        /// 获取所有客户信息--约场成功  产值
        /// </summary>        
        /// <returns></returns>
        public DataTable ReturnTableYuechangProduct(int PowerType, DateTime Startdate, DateTime Enddate)
        {
            string strsql = string.Empty;
            if (PowerType == 0)
                strsql = "select  top 15 CustomerProduct ,COUNT(id) as 'CustomerYuechang'  from V_CrmRptyuechangTuiGuang  group by  CustomerProduct  order by  COUNT(ID) DESC ";
            else
                strsql = "select  top 15 CustomerProduct ,COUNT(id) as 'CustomerYuechang'  from V_CrmRptyuechangTuiGuang   where HmDate between '" + Startdate + "' and '" + Enddate + "' group by  CustomerProduct  order by  COUNT(ID) DESC";
            return ReturnTable(strsql);
        }
        /// <summary>
        /// 获取所有客户信息--约场成功  城市（区域）
        /// </summary>        
        /// <returns></returns>
        public DataTable ReturnTableYuechangCity(int PowerType, DateTime Startdate, DateTime Enddate)
        {
            string strsql = string.Empty;
            if (PowerType == 0)
                strsql = "select  top 15 Province+City as City,COUNT(id) as 'CustomerYuechang'  from V_CrmRptyuechangTuiGuang  group by  Province+City  order by  COUNT(ID) DESC ";
            else
                strsql = "select  top 15 Province+City as City ,COUNT(id) as 'CustomerYuechang'  from V_CrmRptyuechangTuiGuang   where HmDate between '" + Startdate + "' and '" + Enddate + "' group by  Province+City  order by  COUNT(ID) DESC";
            return ReturnTable(strsql);
        }
        #endregion
        #region "成交场"
        /// <summary>
        /// 获取所有客户信息--成交场  行业
        /// </summary>        
        /// <returns></returns>
        public DataTable ReturnTableValueTrade(int PowerType, DateTime Startdate, DateTime Enddate)
        {
            string strsql = string.Empty;
            if (PowerType == 0)
                strsql = "select  top 15 CustomerTrade ,COUNT(id) as 'CustomerYuechang'  from V_CrmRptyuechangTuiGuang  group by  CustomerTrade  order by  COUNT(ID) DESC ";
            else
                strsql = "select  top 15 CustomerTrade ,COUNT(id) as 'CustomerYuechang'  from V_CrmRptyuechangTuiGuang   where HmDate between '" + Startdate + "' and '" + Enddate + "' group by  CustomerTrade  order by  COUNT(ID) DESC";
            return ReturnTable(strsql);
        }
        /// <summary>
        /// 获取所有客户信息--成交场  规模
        /// </summary>        
        /// <returns></returns>
        public DataTable ReturnTableValuePerson(int PowerType, DateTime Startdate, DateTime Enddate)
        {
            string strsql = string.Empty;
            if (PowerType == 0)
                strsql = "select  top 15 CustomerPerson ,COUNT(id) as 'CustomerYuechang'  from V_CrmRptyuechangTuiGuang  where IsValue =1   group by  CustomerPerson  order by  COUNT(ID) DESC ";
            else
                strsql = "select  top 15 CustomerPerson ,COUNT(id) as 'CustomerYuechang'  from V_CrmRptyuechangTuiGuang   where IsValue =1 and HmDate between '" + Startdate + "' and '" + Enddate + "' group by  CustomerPerson  order by  COUNT(ID) DESC";

            return ReturnTable(strsql);
        }
        /// <summary>
        /// 获取所有客户信息--成交场  产值
        /// </summary>        
        /// <returns></returns>
        public DataTable ReturnTableValueProduct(int PowerType, DateTime Startdate, DateTime Enddate)
        {
            string strsql = string.Empty;
            if (PowerType == 0)
                strsql = "select  top 15 CustomerProduct ,COUNT(id) as 'CustomerYuechang'  from V_CrmRptyuechangTuiGuang  where IsValue =1  group by  CustomerProduct  order by  COUNT(ID) DESC ";
            else
                strsql = "select  top 15 CustomerProduct ,COUNT(id) as 'CustomerYuechang'  from V_CrmRptyuechangTuiGuang   where IsValue =1 and HmDate between '" + Startdate + "' and '" + Enddate + "' group by  CustomerProduct  order by  COUNT(ID) DESC";
            return ReturnTable(strsql);
        }
        /// <summary>
        /// 获取所有客户信息--成交场  城市（区域）
        /// </summary>        
        /// <returns></returns>
        public DataTable ReturnTableValueCity(int PowerType, DateTime Startdate, DateTime Enddate)
        {
            string strsql = string.Empty;
            if (PowerType == 0)
                strsql = "select  top 15 Province+City as City,COUNT(id) as 'CustomerYuechang'  from V_CrmRptyuechangTuiGuang  where IsValue =1  group by  Province+City  order by  COUNT(ID) DESC ";
            else
                strsql = "select  top 15 Province+City as City ,COUNT(id) as 'CustomerYuechang'  from V_CrmRptyuechangTuiGuang   where IsValue =1 and HmDate between '" + Startdate + "' and '" + Enddate + "' group by  Province+City  order by  COUNT(ID) DESC";
            return ReturnTable(strsql);
        }
        #endregion

        #region "成交鹰"
        /// <summary>
        /// 获取所有客户信息--成交鹰  行业
        /// </summary>        
        /// <returns></returns>
        public DataTable ReturnTableYingTrade(int PowerType, DateTime Startdate, DateTime Enddate)
        {
            string strsql = string.Empty;
            if (PowerType == 0)
                strsql = "select  top 15 CustomerTrade ,COUNT(CustomerName) as 'CustomerYuechang'  from V_CrmRptYingTuiguang  group by  CustomerTrade  order by  COUNT(CustomerName) DESC ";
            else
                strsql = "select  top 15 CustomerTrade ,COUNT(CustomerName) as 'CustomerYuechang'  from V_CrmRptYingTuiguang   where Date between '" + Startdate + "' and '" + Enddate + "' group by  CustomerTrade  order by  COUNT(CustomerName) DESC";
            return ReturnTable(strsql);
        }
        /// <summary>
        /// 获取所有客户信息--成交鹰  规模
        /// </summary>        
        /// <returns></returns>
        public DataTable ReturnTableYingPerson(int PowerType, DateTime Startdate, DateTime Enddate)
        {
            string strsql = string.Empty;
            if (PowerType == 0)
                strsql = "select  top 15 CustomerPerson ,COUNT(CustomerName) as 'CustomerYuechang'  from V_CrmRptYingTuiguang      group by  CustomerPerson  order by  COUNT(CustomerName) DESC ";
            else
                strsql = "select  top 15 CustomerPerson ,COUNT(CustomerName) as 'CustomerYuechang'  from V_CrmRptYingTuiguang    where Date between '" + Startdate + "' and '" + Enddate + "' group by  CustomerPerson  order by  COUNT(CustomerName) DESC";

            return ReturnTable(strsql);
        }
        /// <summary>
        /// 获取所有客户信息--成交鹰  产值
        /// </summary>        
        /// <returns></returns>
        public DataTable ReturnTableYingProduct(int PowerType, DateTime Startdate, DateTime Enddate)
        {
            string strsql = string.Empty;
            if (PowerType == 0)
                strsql = "select  top 15 CustomerProduct ,COUNT(CustomerName) as 'CustomerYuechang'  from V_CrmRptYingTuiguang    group by  CustomerProduct  order by  COUNT(CustomerName) DESC ";
            else
                strsql = "select  top 15 CustomerProduct ,COUNT(CustomerName) as 'CustomerYuechang'  from V_CrmRptYingTuiguang   where  Date between '" + Startdate + "' and '" + Enddate + "' group by  CustomerProduct  order by  COUNT(CustomerName) DESC";
            return ReturnTable(strsql);
        }
        /// <summary>
        /// 获取所有客户信息--成交鹰  城市（区域）
        /// </summary>        
        /// <returns></returns>
        public DataTable ReturnTableYingCity(int PowerType, DateTime Startdate, DateTime Enddate)
        {
            string strsql = string.Empty;
            if (PowerType == 0)
                strsql = "select  top 15 Province+City as City,COUNT(CustomerName) as 'CustomerYuechang'  from V_CrmRptYingTuiguang    group by  Province+City  order by  COUNT(CustomerName) DESC ";
            else
                strsql = "select  top 15 Province+City as City ,COUNT(CustomerName) as 'CustomerYuechang'  from V_CrmRptYingTuiguang    where Date between '" + Startdate + "' and '" + Enddate + "' group by  Province+City  order by  COUNT(CustomerName) DESC";
            return ReturnTable(strsql);
        }
        #endregion
        #region "成交项目"
        /// <summary>
        /// 获取所有客户信息--成交项目  行业
        /// </summary>        
        /// <returns></returns>
        public DataTable ReturnTableProjectTrade(int PowerType, DateTime Startdate, DateTime Enddate)
        {
            string strsql = string.Empty;
            if (PowerType == 0)
                strsql = "select  top 15 CustomerTrade ,COUNT(CustomerName) as 'CustomerYuechang'  from V_CrmRptProjectTuiguang  group by  CustomerTrade  order by  COUNT(CustomerName) DESC ";
            else
                strsql = "select  top 15 CustomerTrade ,COUNT(CustomerName) as 'CustomerYuechang'  from V_CrmRptProjectTuiguang   where Date between '" + Startdate + "' and '" + Enddate + "' group by  CustomerTrade  order by  COUNT(CustomerName) DESC";
            return ReturnTable(strsql);
        }
        /// <summary>
        /// 获取所有客户信息--成交项目  规模
        /// </summary>        
        /// <returns></returns>
        public DataTable ReturnTableProjectPerson(int PowerType, DateTime Startdate, DateTime Enddate)
        {
            string strsql = string.Empty;
            if (PowerType == 0)
                strsql = "select  top 15 CustomerPerson ,COUNT(CustomerName) as 'CustomerYuechang'  from V_CrmRptProjectTuiguang    group by  CustomerPerson  order by  COUNT(CustomerName) DESC ";
            else
                strsql = "select  top 15 CustomerPerson ,COUNT(CustomerName) as 'CustomerYuechang'  from V_CrmRptProjectTuiguang   where  Date between '" + Startdate + "' and '" + Enddate + "' group by  CustomerPerson  order by  COUNT(CustomerName) DESC";

            return ReturnTable(strsql);
        }
        /// <summary>
        /// 获取所有客户信息--成交项目  产值
        /// </summary>        
        /// <returns></returns>
        public DataTable ReturnTableProjectProduct(int PowerType, DateTime Startdate, DateTime Enddate)
        {
            string strsql = string.Empty;
            if (PowerType == 0)
                strsql = "select  top 15 CustomerProduct ,COUNT(CustomerName) as 'CustomerYuechang'  from V_CrmRptProjectTuiguang    group by  CustomerProduct  order by  COUNT(CustomerName) DESC ";
            else
                strsql = "select  top 15 CustomerProduct ,COUNT(CustomerName) as 'CustomerYuechang'  from V_CrmRptProjectTuiguang   where  Date between '" + Startdate + "' and '" + Enddate + "' group by  CustomerProduct  order by  COUNT(CustomerName) DESC";
            return ReturnTable(strsql);
        }
        /// <summary>
        /// 获取所有客户信息--成交项目  城市（区域）
        /// </summary>        
        /// <returns></returns>
        public DataTable ReturnTableProjectCity(int PowerType, DateTime Startdate, DateTime Enddate)
        {
            string strsql = string.Empty;
            if (PowerType == 0)
                strsql = "select  top 15 Province+City as City,COUNT(CustomerName) as CustomerYuechang  from V_CrmRptProjectTuiguang    group by  Province+City  order by  COUNT(CustomerName) DESC ";
            else
                strsql = "select  top 15 Province+City as City ,COUNT(CustomerName) as CustomerYuechang  from V_CrmRptProjectTuiguang   where  Date between '" + Startdate + "' and '" + Enddate + "' group by  Province+City  order by  COUNT(CustomerName) DESC";
            return ReturnTable(strsql);
        }
        #endregion
        /// <summary>
        /// 返回DataTabel
        /// </summary>
        /// <param name="select"></param>
        /// <returns></returns>
        private DataTable ReturnTable(string select)
        {
            DataTable dtcustomer = new DataTable();
            DbSql execsql = new DAL.DBExtend.DbSql();
            dtcustomer = execsql.ExecuteQuery(select);
            return dtcustomer;
        }
    }
}
